package jdbc;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.cassandra.utils.Hex;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class JDBCTest {

	private Connection con = null;
	/**
	 * create keyspace
	 * create columnfamily
	 * @throws SQLException
	 */
	@Before
	public void setUp() throws SQLException{

		String host=System.getProperty("url","jdbc:laud://localhost:9170?data_port=9091&support_function=true&hive=false");
		try {
			Class.forName("cn.edu.thu.laud.jdbc.LaUDDriver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		con = DriverManager.getConnection(host, "", "");
		Statement statement=con.createStatement();
		try{
			statement.execute("drop keyspace hxd_testks1");
		}catch(Exception e){

		}
		statement.execute("create keyspace  hxd_testks1 with  strategy_class ='org.apache.cassandra.locator.SimpleStrategy' and strategy_options:replication_factor=1;");
		statement.execute("use hxd_testks1");
		statement.execute("create columnfamily user1 (id varchar primary key,age int,name varchar,imagefeature blob,photo 'hblob',sphoto 'sblob')");
		statement.close();
	}
	/**
	 * create index
	 * @throws SQLException
	 */
	@Test
	public void a_createIndex() throws SQLException{

		Statement statement = con.createStatement();
		statement.execute("create index user1_age_idx on user1(age)"); 

//		statement.execute("create index on user1(name) " +
//				"with " 
//				+"'class_name'='cn.edu.thu.laud.lasql.index.LaUDIndex'" 
//				+"and 'implemention_class'='cn.thu.thss.laud.index.solr.LaudSolrIndex'" 
//				+"and 'featureType'='1'");

		//image index only can access data on 192.168.106.22*,and it seems that this index only support one instance.
		//and this index can not update. we should insert data first,and then drop old index ,then create index again.
//		statement.execute("create index on user1(imagefeature) " +
//				"with " 
//				+"'class_name'='cn.edu.thu.laud.lasql.index.LaUDIndex'" 
//				+"and 'implemention_class'='cn.edu.thu.laud.LaUDDistributedHDSearcherForIntegration.LaUDDistributedHDIndexerImage'" 
//				+"and 'featureType'='3'");

		statement.close();
	}
	/**
	 * insert  normal data
	 * <br> include insert int,string,bytes,function
	 * @throws SQLException
	 */
	@Test
	public void b_insertNomalData() throws SQLException{

		Statement statement = con.createStatement();
		statement.execute("insert into user1(id,name,age) values (1,'测试人员1',22)"); 
		statement.execute("insert into user1(id,name,age) values (2,'测试人员2',23)");	
		//create a image feature.
		byte[] bytes=new FeatureFunction().createImageFeature();
		//use byteToHex to insert blob.
		statement.execute(String.format("insert into user1(id,name,age,imagefeature) values (3,'人员3',23,'%s')",Hex.bytesToHex(bytes)));	

		//we can also use local function insert
		statement.execute("insert into user1(id,name,age,imagefeature) values (4,'人员4',23,function('jdbc.FeatureFunction','param1','andSoOn'))");
		
		
		
		
		statement.close();
		
	}
	
	
	@Test
	public void c_insertFile() throws SQLException{

		
		
		Statement statement = con.createStatement();
		
		

		
		//should make sure that the file is existed.
		statement.execute("insert into user1(id,name,photo,sphoto) values (5,'yiguolei','g:/test/1.jpg','g:/test/6.jpg')"); //,'F:/lib/laud-thrift-1.0.7-1.jar'
		ResultSet rsResultSet = statement.getResultSet();
		while(rsResultSet.next()){
			rsResultSet.updateBlob("photo", System.in);
			rsResultSet.updateBlob("sphoto", System.in);
		}
		rsResultSet.close();
		statement.close();
	}
	@Test
	public void getBlob() throws SQLException{

		Statement statement = con.createStatement();
		statement.execute("select id,name,photo,sphoto from user1");
		ResultSet rsResultSet = statement.getResultSet();
		while(rsResultSet.next()){
			//hblob ,lblob .we will save them automaticly..
			rsResultSet.getBlob("photo");
			/*
			//if we want to save in a designed location.we should do like this:
			 ((FileTransfer)rsResultSet).getBlob("photo","e:/test/");
			 */
			//with sblob,we do not save them in file . we return a java.sql.Blob object.
			
			Blob blob=rsResultSet.getBlob("cphoto");
			/*
			 //we can save this blob as a file like this:
			if(blob!=null){
				InputStream iStream=blob.getBinaryStream();
				writeIntoFile(iStream, "a.jpg");
			}
			*/	
		}
		//remeber close ResultSet !!!!!!
		rsResultSet.close();
		statement.close();
		
		
	}
	@Test
	public void d_select() throws SQLException{

		Statement statement = con.createStatement();
		ResultSet rSet;
		//use function
		//this function return the second parameter.(in this case it return 测试人员1)
//		statement.execute("select * from user1 where name=function('cn.edu.thu.laud.lasql.function.iface.impl.test.TestFunction','测试人员1','ceshi2')");
//		rSet=statement.getResultSet();
//		while(rSet.next()){
//			System.out.println(rSet.getObject(1));
//		}
//		rSet.close();
//		statement.close();
		statement.execute("select * from user1");
		rSet=statement.getResultSet();
		System.out.println("begin to select");
		while(rSet.next()){
			System.out.println(rSet.getObject(1));
		}
		System.out.println("end select");
		rSet.close();
		statement.close();
	}
	@Test
	public void e_dropIndex() throws SQLException{

		Statement statement = con.createStatement();
		//if we did not claim index name when we create the index, the index name will be like this "<cfName>_<colName>_idx"
		statement.execute("drop index user1_age_idx");
		statement.close();
	}
	
	//@After
	@Test
	public void f_end() throws SQLException{
		try{
			Statement statement = con.createStatement();
			statement.execute("drop keyspace hxd_testks1");
			statement.close();
		}finally{
			con.close();
		}
	}
}
